package cs341.cs.uwosh.edu.jBeats;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.tika.exception.TikaException;
import org.apache.tika.metadata.Metadata;
import org.apache.tika.parser.ParseContext;
import org.apache.tika.parser.Parser;
import org.apache.tika.parser.mp3.Mp3Parser;
import org.xml.sax.ContentHandler;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

/**
 * databaseInfo class
 * This class connects to a specific database and can make specific calls to
 * get information from the database or update info
 * @author haibut41, schled84
 */
public class DatabaseInfo {
    static String dbURL;
    static Connection conn;
      
    /**
     * Empty intantiator for database info class
     */
    DatabaseInfo(){
        
    }
    
    /**
     * @return String array of registered users in the database
     */
        public static User[] getUserList(){
        connect();
        User[] ret = null;
        try{
            String query = "select userName from user";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int count = 0;
            while(rs.next()){
                count++;
            }
            
            User [] results = new User[count];

            query = "select * from user";
            st = conn.createStatement();
            rs = st.executeQuery(query);
            count=0;
            while(rs.next()){
                results[count]= new User(rs.getString("userName"));
                results[count].setFavoriteBand(rs.getString("favBand"));
                results[count].setFavoriteGenre(rs.getString("genre"));
                results[count].setFavoriteSong(rs.getString("favSong"));
                results[count].setLocation(rs.getString("location"));
                results[count].setAge(rs.getInt("age"));
                results[count].setGender(rs.getString("gender"));
                count++;
            }
            ret = results;
            conn.close();

        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        return ret;
    }
    
    /**
     * @param username, playlistKey: playlistKey = master, PL1, PL2, PL3
     * @return String array of all the songs in a specific playlist for a user
     */
    public static Song[] getSongList(String username, String playlistKey){//static SongObject[] getSongList()
        connect();
        Song[] ret = null;
        try{
            String query = "select PL1, PL2, PL3 from user where userName='"+username+"';";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next()){
                if (rs.getString("PL1").equals(playlistKey))
                    playlistKey = "PL1";
                else if (rs.getString("PL2").equals(playlistKey))
                    playlistKey = "PL2";
                else if (rs.getString("PL3").equals(playlistKey))
                    playlistKey = "PL3";
                else
                    playlistKey = "master";
            } 
            query = "select name from masterPlaylist a left join userPlaylist b on a.id=b.songKey where b."+playlistKey+"=1 and b.user='"+username+"';";
            st = conn.createStatement();
            rs = st.executeQuery(query);
            int count = 0;
            while(rs.next()){
                count++;
            }

            
            query = "select * from masterPlaylist a left join userPlaylist b on a.id=b.songKey where b."+playlistKey+"=1 and b.user='"+username+"';";
            Song [] results = new Song[count];
            st = conn.createStatement();
            rs = st.executeQuery(query);
            count=0;
            while(rs.next()){
                results[count] = new Song();
                results[count].setName(rs.getString("name"));
                results[count].setAlbum(rs.getString("album"));
                results[count].setArtist(rs.getString("artist"));
                results[count].setGenre(rs.getString("genre"));
                results[count].setRating(rs.getDouble("userRating"));
                results[count].setFilePath(rs.getString("path"));
                results[count].setID(rs.getInt("id"));
                //results[count].setAverageRating(rs.getDouble("rating"));
                double temp = rs.getDouble("rating");
                temp = temp*100;
                temp = Math.round(temp);
                temp = temp / 100;
                results[count].setAverageRating(temp);
                count++;
            }
            ret = results;
            conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        return ret;     
        
    }
    
    /**
     * @param username
     * @return String array of playlist names for username
     */
    public static String[] getPlaylists(String username){
        connect();
        String[] ret = new String[3];
        try{
            String query = "select PL1, PL2, PL3 from user where userName='"+username+"';";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            rs.next();
            ret[0] = rs.getString("PL1");
            ret[1] = rs.getString("PL2");
            ret[2] = rs.getString("PL3");
            conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        return ret;
    }
    
    /**
     * @param username
     * @return String array of the reviews from a specific user
     */
    public static String[] getUserReviews(String username){
        connect();
        String[] ret = null;
        try{
            String query = "select * from review a left join masterPlaylist b on a.songKey=b.id where user='"+username+"';";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int count = 0;
            while(rs.next()){
                count++;
            }
            
            String [] results = new String[count];
            st = conn.createStatement();
            rs = st.executeQuery(query);
            count=0;
            while(rs.next()){
                results[count] = rs.getString("name")+": "+rs.getString("review");
                count++;
            }
            ret = results;
            conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        return ret;

    }
    
    /**
     * @param String song
     * @return String array of all the reviews for a specific song
     */
    public static String[] getSongReviews(String song){
        connect();
        String[] ret = null;
        try{
            String query = "select * from review a left join masterPlaylist b on a.songKey=b.id where name='"+song+"';";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int count = 0;
            while(rs.next()){
                count++;
            }
            
            String [] results = new String[count];
            st = conn.createStatement();
            rs = st.executeQuery(query);
            count=0;
            while(rs.next()){
                results[count] = rs.getString("user")+":  "+rs.getString("review");
                count++;
            }
            ret = results;
            conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        return ret;
    }
    
    /**
     * @param username
     * @return a User with specific information for a user
     */
    public static User getUserInfo(String username){
        User user = new User(username);
        connect();
        try{
            user.setUserName(username);
            String query = "select * from user where userName='"+username+"';";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next()){
                user.setAge(rs.getInt("age"));
                user.setFavoriteBand(rs.getString("favBand"));
                user.setFavoriteGenre(rs.getString("genre"));
                user.setFavoriteSong(rs.getString("favSong"));
                user.setGender(rs.getString("gender"));
                user.setLocation(rs.getString("location"));
            }

            conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        
        return user;
      }
    
    /**
     * @param username
     * @return pass: the password for the given username
     */
    public static String getPassword(String username){
        String pass = null;
        connect();
        try{
          String query = "select password from user where userName='"+username+"';";
          Statement st = conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while(rs.next()){
            pass = rs.getString("password");
          }
          conn.close();
        } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        
        return pass;
    }
    
    /**
     * Adds a song to the Database
     * Note: Will not add song with "'" at the end of song name
     * @param path: String of file path using / separator
     * @return True if success, false if fail
     * @throws FileNotFoundException
     * @throws IOException
     * @throws SAXException
     * @throws TikaException 
     */
    public static boolean addSong(String path) throws FileNotFoundException, IOException, SAXException, TikaException{
        connect();
        File songFile = new File(path);
        if (!songFile.exists() || !path.contains(".mp3")){    //Not an mp3 file
            System.out.print("File Doesn't Exist");
            return false;
        } 
        else{         //An actual mp3 file
            try {     
                //creates metadata for song
                String check = "select id, path from masterPlaylist";
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(check);
                int indexID = 1;
                boolean on = true;
                while(rs.next()){
                    if ((rs.getString("path").equalsIgnoreCase(path))){
                        System.out.println("Error: Song Already Entered");
                        return false;
                    }
                    if (rs.getInt("id")-1>=indexID && on){
                        on = false;
                    } 
                    if (on){
                        indexID++;
                    }
                    
                }
                
                Metadata metadata;
                InputStream input = new FileInputStream(new File(path));
                ContentHandler handler = new DefaultHandler();
                metadata = new Metadata();
                Parser parser = new Mp3Parser();
                ParseContext parseCtx = new ParseContext();
                parser.parse(input, handler, metadata, parseCtx);
                  
                //Add to master playlist
                String query = "insert into masterPlaylist values ("+indexID
                        +", '"+metadata.get("title")+"', '"+path+"', 0, 0, '"+metadata.get("Author")+"', '"+
                        metadata.get("xmpDM:genre")+"', '"+metadata.get("xmpDM:album")+"');";
                st = conn.createStatement();
                int executeUpdate = st.executeUpdate(query);
                
                //Add to userPlaylist
                check = "select userName from user";
                st = conn.createStatement();
                rs = st.executeQuery(check);
                while(rs.next()){
                    query = "insert into userPlaylist values('"+rs.getString("userName")+"', "+indexID+
                            ", 0, 1, 0, 0, 0);";
                    Statement st2 = conn.createStatement();
                    executeUpdate = st2.executeUpdate(query);
                }
                
                
                conn.close();
            } catch (SQLException ex) {Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);}
        }
        return true;
    }
    
    public static boolean updateSongInfo(String u, Song song){
        String query = "update masterPlaylist set name='"+song.getName()+"', artist='"+song.getArtist()+"', genre='"+song.getGenre()
                +"', album='"+song.getAlbum()+"' where id="+song.getSongID()+";";
        boolean a = updateStatement(query);
        boolean b = addRating(u, song.getRating(), song.getSongID());
        
        return a&&b;
    }
    
    /**
     * Deletes a song in a user's playlist
     * @param user
     * @param playlistKey: PL1, PL2, PL3, or master
     * @param songKey: int id for song
     * @return True if success, false if fail
     */
    public static boolean deletePlaylistSong(String user, String playlistKey, int songKey) throws SQLException{
        connect();
        String query = "select PL1, PL2, PL3 from user where userName='"+user+"';";
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        while(rs.next()){
            if (rs.getString("PL1").equals(playlistKey))
                playlistKey = "PL1";
            else if (rs.getString("PL2").equals(playlistKey))
                playlistKey = "PL2";
            else if (rs.getString("PL3").equals(playlistKey))
                playlistKey = "PL3";
            else
                playlistKey = "master";
        }
        conn.close();
        
        query = "update userPlaylist set "+playlistKey+"=0 where user='"+user
                +"' and songKey= "+songKey+";";
        return updateStatement(query);
    }
    
    /**
     * Adds a song to a specific playlist
     * @param user
     * @param playlistKey: PL1, PL2, PL3, or master
     * @param songKey: int id for song
     * @return True if success, false if fail
     */
    public static boolean addPlaylistSong(String user, String playlistKey, int songKey){
        String query = "select PL1, PL2, PL3 from user where userName='"+user+"';";
        try {
            connect();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next()){
                if (rs.getString("PL1").equals(playlistKey))
                    playlistKey = "PL1";
                else if (rs.getString("PL2").equals(playlistKey))
                    playlistKey = "PL2";
                else if (rs.getString("PL3").equals(playlistKey))
                    playlistKey = "PL3";
                else
                    playlistKey = "master";
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = "update userPlaylist set "+playlistKey+"=1 where user='"+user
                    +"' and songKey= "+songKey+";";
        return updateStatement(query);
    }
    
    /**
     * Deletes a song for a specific user
     * @param user
     * @param songKey
     * @return True if success, false if fail
     */
    public static boolean deleteUserSong(String user, int songKey){
        String query = "update userPlaylist set master=0, PL1=0, PL2=0; PL3=0 where user='"
                +user+"' and songKey= "+songKey+";";
        return updateStatement(query);
    }
    
    /**
     * Deletes a song from database
     * @param songKey
     * @return True if success, false if fail
     */
    public static boolean deleteSong(int songKey){
        String query = "delete from userPlaylist where songKey="+songKey+";";
        boolean a = updateStatement(query);
        query = "delete from masterPlaylist where id="+songKey+";";
        boolean b = updateStatement(query);
        query = "delete from review where songKey="+songKey+";";
        boolean c = updateStatement(query);
        return a && b && c;
    }
    
    /**
     * Updates the rating of a song
     * @param user
     * @param rating
     * @param songKey
     * @return 
     */
    private static boolean addRating(String user, double rating, int songKey){
        double masRate = 0;
        
        String query = "update userPlaylist set userRating="+rating+" where user='"+user+"' and songKey="+songKey+";";
        boolean b = updateStatement(query);
        
        
        query = "select userRating from userPlaylist where songKey="+songKey+";";
        List<Double> l = new ArrayList<Double>(); 
        try {
            connect();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()){
                l.add(rs.getDouble("userRating"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        int count = l.size();
        for(Double x:l){
            masRate+=x;
            if(x<1)
                count--;
        }
        //recalculate ratings

        masRate/=count;

        
        //update database
        query = "update masterPlaylist set rating="+masRate+" where id="+songKey+";";
        boolean a = updateStatement(query);
        
        return a && b;
    }
    
    /**
     * Adds a review to the database
     * @param user
     * @param songKey
     * @param Review
     * @return True if success, false if fail
     */
    public static boolean addReview(String user, int songKey, String review){
        String query = "insert into review values('"+user+"', "+songKey+", '"+review+"');";
        return updateStatement(query);
    }
    
    public static boolean isAdmin(String username) throws SQLException{
        connect();
        String query = "select admin from user where userName='"+username+"';";
          Statement st = conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while(rs.next()){
            return rs.getBoolean("admin");
          }
          return rs.getBoolean("admin");
    }
    
    /**
     * Adds a user to the database
     * @param user
     * @param password
     * @return True if success, false if fail
     */
    public static boolean addUser(String user, String password){
        String query = "insert into user values('"+user+"', '"+password+"', null, 0, null, null, 0, null, null, 'newPlaylistOne', 'newPlaylistTwo', 'newPlaylistThree');";
        boolean a = updateStatement(query);
        
        try {
            connect();
            String check = "select id from masterPlaylist";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(check);
            while (rs.next()){
                query = "insert into userPlaylist values('"+user+"', "+rs.getInt("id")+", 0, 1, 0, 0, 0);";
                updateStatement(query);
            }
        
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;
    }
    
    /**
     * makes a user an administrator
     * @param user
     * @return 
     */
    public static boolean makeAdmin(String user){
        String query = "update user set admin=1 where userName='"+user+"';";
        return updateStatement(query);
    }
    
    
    
    /**
     * Adds a favorite genre for a specified user
     * @param user
     * @param genre
     * @return 
     */
    public static boolean updateUserGenre(String user, String genre){
        String query = "update user set genre='"+genre+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds a favorite band for a specified user
     * @param user
     * @param band
     * @return 
     */
    public static boolean updateUserBand(String user, String band){
        String query = "update user set favBand='"+band+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds a favorite song for a specified user
     * @param user
     * @param song
     * @return 
     */
    public static boolean updateUserFavSong(String user, String song){
        String query = "update user set favSong='"+song+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds an age for a specified user
     * @param user
     * @param age
     * @return 
     */
    public static boolean updateUserAge(String user, int age){
        String query = "update user set age='"+age+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds a gender for a specified user
     * @param user
     * @param gender
     * @return 
     */
    public static boolean updateUserGender(String user, String gender){
        String query = "update user set gender='"+gender+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds a location for a specified user
     * @param user
     * @param location
     * @return 
     */
    public static boolean updateUserLocation(String user, String location){
        String query = "update user set location='"+location+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    /**
     * Adds a name for a playlist for a specified user
     * @param user
     * @param name
     * @param playlistKey
     * @return 
     */
    public static boolean updateUserPlaylist(String user, String newName, String oldName) {
        String query = "select PL1, PL2, PL3 from user where userName='"+user+"';";
        try {
            connect();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next()){
                if (rs.getString("PL1").equals(oldName))
                    oldName = "PL1";
                else if (rs.getString("PL2").equals(oldName))
                    oldName = "PL2";
                else if (rs.getString("PL3").equals(oldName))
                    oldName = "PL3";
                else
                    oldName = "master";
            }
            conn.close();
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = "update user set "+oldName+"='"+newName+"' where userName='"+user+"';";
        return updateStatement(query);
    }
    
    public static boolean deleteUser(String user){
        String query = "delete from review where user='"+user+"';";
        boolean a = updateStatement(query);
        
        query = "delete from user where userName='"+user+"';";
        boolean b = updateStatement(query);
        
        query = "delete from userPlaylist where user='"+user+"';";
        boolean c = updateStatement(query);
        
        return a && b && c;
    }
    
    //---------------------------------------------------------------------------------------------------//
    // internal methods
    
    /**
     * performs an update query on database
     * @param query
     * @return 
     */
    private static boolean updateStatement(String query){
        connect();
        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInfo.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    /**
     * Connects to a specific database for this project. 
     * Does not close the connection
     */
    private static void connect(){
      try
      {
 //         String[] cred = new String[3];
          String[] cred = {"cs341g6","cs341g6","g981158"};
//                try {
//
//                       // FileReader in = new FileReader("/students/haibut41/NetBeansProjects/jBeats/src/cs341/cs/uwosh/edu/jBeats/Config.txt");
//                      FileReader in = new FileReader("/Config.txt");  
//                    BufferedReader buff = new BufferedReader(in);
//                        String currLine;
//                        int c = 0;
//                        currLine = buff.readLine();
//                        while(currLine != null)
//                        {
//                                cred[c] = currLine;
//                                currLine = buff.readLine();
//                                c++;
//                        }
//                } catch (IOException x) { }

          Class.forName("com.mysql.jdbc.Driver");  // register the driver
          dbURL = "jdbc:mysql://cs341.cs.uwosh.edu:4381/"+cred[0];
          //System.out.println("Attempting connection");
          conn = DriverManager.getConnection(dbURL,cred[1],cred[2]);
          //System.out.println("Connection successful");
          
      }
      catch (SQLException e) {System.out.println("SQL Error " + e.getMessage());}
      catch (Exception e) {System.out.println("General Error " + e.getMessage());}
        
    }
}